import typing

import pymysql

from sai import Pipeline, Item
from sai.utils import logger


class PymysqlPipeline(Pipeline):
    '''Biuldin PymysqlPipelin,
    checkout pymysql_pipeline_usage_login2'''
    def __init__(self,
                 *,
                 host: str='127.0.0.1',
                 user: str='',
                 password: str='',
                 port: int=3306,
                 db_name: str='',
                 table_name: str='',
                 charset='utf8mb4'):
        self.host = host
        self.user = user
        self.password = password
        self.port = port
        self.db_name = db_name
        self.table_name = table_name
        self.charset = charset

        self.conn: pymysql.Connection = None

    def open_spider(self, spider):
        self.conn = pymysql.connect(host=self.host,
                                  user=self.user,
                                  password=self.password,
                                  port=self.port,
                                  database=self.db_name,
                                  charset=self.charset)


    def process_item(self, item: typing.Union[Item, dict], spider):
        with self.conn.cursor() as cursor:
            keys = ', '.join(item.keys())
            values = ', '.join(['%s'] * len(item))
            # sql = f'insert into {self.table_name}({keys}) values({values})'
            sql = f'replace into {self.table_name}({keys}) values({values})'  # prevent dumplication

            print_values = ', '.join(item.values())
            print_sql = f'replace into {self.table_name}({keys}) values({print_values})'

            try:
                if cursor.execute(sql, tuple(item.values())):
                    self.conn.commit()
                    logger.info(f'Insertion succeeded ---> {print_values}')
            except Exception as e:
                logger.info(f'Insertion failed ---> {print_values}')
                logger.error(e)
                self.conn.rollback()

    def close_spider(self, spider):
        self.conn.close()
